Skip Headers

Oracle® OLAP DML Reference 10g Release 2 (10.2) Part Number B14346-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# Creating Models

To create an OLAP DML model, take the following steps:

1. Issue a DEFINE MODEL statement to define the program object.

2. Add a specification to the model to specify the processing that you want performed as described in MODEL.

3. Compile the model as described in "Compiling Models".

4. (Optional) If necessary, change the settings of model options listed in Table A-7, "Model Options".

5. Execute the model as described in "Running a Model".

6. Debug the model as described in "Debugging a Model".

7. When you want the model to be a permanent part of the analytic workspace, save the model using an UPDATE statement followed by COMMIT.

For an example of creating a model, see "Creating a Model".

## Nesting Models

You can include one model within another model by using an INCLUDE statement. The model that contains the INCLUDE statement is referred to as the parent model. The included model is referred to as the base model. You can nest models by placing an INCLUDE statement in a base model. For example, model `myModel1` can include model `myModel2`, and model `myModel2` can include model `myModel3`. The nested models form a hierarchy. In this example, `myModel1` is at the top of the hierarchy, and `myModel3` is at the root.

When a model contains an INCLUDE statement, then it cannot contain any DIMENSION (in models) statements. A parent model inherits its dimensions, if any, from the DIMENSION statements in the root model of the included hierarchy. In the example just given, models `myModel1` and `myModel2` both inherit their dimensions from the DIMENSION statements in model `myModel3`.

The INCLUDE statement enables you to create modular models. When certain equations are common to several models, then you can place these equations in a separate model and include that model in other models as needed.

The INCLUDE statement also facilitates what-if analyses. An experimental model can draw equations from a base model and selectively replace them with new equations. To support what-if analysis, you can use equations in a model to mask previous equations. The previous equations can come from the same model or from included models. A masked equation is not executed or shown in the MODEL.COMPRPT report for a model

## Dimension Status and Model Equations

When a model contains an assignment statement to assign data to a dimension value, then the dimension is limited temporarily to that value, performs the calculation, and restores the initial status of the dimension.

For example, a model might have the following statements.

    DIMENSION line
    gross.margin = revenue - cogs

If you specify `actual` as the solution variable when you run the model, then the following code is constructed and executed.

    PUSH line
    LIMIT line TO gross.margin
    actual = actual(line revenue) - actual(line cogs)
    POP line

This behind-the-scenes construction lets you perform complex calculations with simple model equations. For example, line item data might be stored in the `actual` variable, which is dimensioned by `line`. However, detail line item data might be stored in a variable named `detail.data`, with a dimension named `detail.line`.

When your analytic workspace contains a relation between `line` and `detail.line`, which specifies the line item to which each detail item pertains, then you might write model equations such as the following ones.

    revenue = total(detail.data line)
    expenses = total(detail.data line)

The relation between `detail.line` and `line` is used automatically to aggregate the detail data into the appropriate line items. The code that is constructed when the model is run ensures that the appropriate total is assigned to each value of the `line` dimension. For example, while the equation for the `revenue` item is calculated, `line` is temporarily limited to `revenue`, and the `TOTAL` function returns the total of detail items for the `revenue` value of `line`.

## Using Data from Past and Future Time Periods

Several OLAP DML functions make it easy for you to use data from past or future time periods. For example, the `LAG` function returns data from a specified previous time period, and the `LEAD` function returns data from a specified future period.

When you run a model that uses past or future data in its calculations, you must make sure that your solution variable contains the necessary past or future data. For example, a model might contain an assignment statement that bases an estimate of the `revenue` line item for the current month on the `revenue` line item for the previous month.

    DIMENSION line month
    ...
    revenue = LAG(revenue, 1, month) * 1.05

When the `month` dimension is limited to `Apr2004` to `Jun2004` when you run the model, then you must be sure that the solution variable contains `revenue` data for `Mar96`.

When your model contains a `LEAD` function, then your solution variable must contain the necessary future data. For example, when you want to calculate data for the months of April through June of 2004, and when the model retrieves data from one month in the future, then the solution variable must contain data for July 2004 when you run the model.

## Handling NA Values in Models

Oracle OLAP observes the NASKIP2 option when it evaluates equations in a model. NASKIP2 controls how `NA` values are handled when `+` (plus) and `-` (minus) operations are performed. The setting of NASKIP2 is important when the solution variable contains `NA` values.

The results of a calculation may be `NA` not only when the solution variable contains an `NA` value that is used as input, but also when the target of a simultaneous equation is `NA`. Values in the solution variable are used as the initial values of the targets in the first iteration over a simultaneous block. Therefore, when the solution variable contains `NA` as the initial value of a target, an `NA` result may be produced in the first iteration, and the `NA` result may be perpetuated through subsequent iterations.

To avoid obtaining `NA` for the results, you can make sure that the solution variable does not contain `NA` values or you can set NASKIP2 to `YES` before running the model.

## Solving Simultaneous Equations

An iterative method is used to solve the equations in a simultaneous block. In each iteration, a value is calculated for each equation, and compares the new value to the value from the previous iteration. When the comparison falls within a specified tolerance, then the equation is considered to have converged to a solution. When the comparison exceeds a specified limit, then the equation is considered to have diverged.

When all the equations in the block converge, then the block is considered solved. When any equation diverges or fails to converge within a specified number of iterations, then the solution of the block (and the model) fails and an error occurs.

You can exercise control over the solution of simultaneous equations, use the OLAP DML options described in Table A-7, "Model Options". For example, using these options, you can specify the solution method to use, the factors to use in testing for convergence and divergence, the maximum number of iterations to perform, and the action to take when the assignment statement diverges or fails to converge.

## Modeling for Multiple Scenarios

Instead of calculating a single set of figures for a month and division, you might want to calculate several sets of figures, each based on different assumptions.

You can define a scenario model that calculates and stores forecast or budget figures based on different sets of input figures. For example, you might want to calculate profit based on optimistic, pessimistic, and best-guess figures.

To build a scenario model, follow these steps.

1. Define a scenario dimension.

2. Define a solution variable dimensioned by the scenario dimension.

3. Enter input data into the solution variable.

4. Write a model to calculate results based on the input data.

For an example of building a scenario model see, Example 17-12, "Building a Scenario Model".